package com.zhiqingchun.dao.comment.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.zhiqingchun.common.Page;
import com.zhiqingchun.dao.BaseDao;
import com.zhiqingchun.dao.article.ArticleResult;
import com.zhiqingchun.dao.comment.CommentResult;
import com.zhiqingchun.dao.comment.ICommentDao;
import com.zhiqingchun.pojo.ArticleDO;
import com.zhiqingchun.pojo.CommentDO;
import com.zhiqingchun.pojo.Usr;

public class CommentDao extends BaseDao implements ICommentDao {

	@Override
	public CommentResult byId(int id) {
		String sql = "select c.*, u.usr, u.avatar  from comment c, usr u "
				+ "where c.article_id = ? and c.usr_id = u.id order by c.time";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			List<CommentDO> commentList = new ArrayList<CommentDO>();
			while (rs.next()) {
				CommentDO comment = new CommentDO();
				comment.setUsrName(rs.getString("USR"));
				comment.setUsrId(rs.getInt("USR_ID"));
				comment.setContent(rs.getString("CONTENT"));
				comment.setTime(rs.getTimestamp("TIME"));
				comment.setAvatar(rs.getString("AVATAR"));
				comment.setId(rs.getInt("ID"));
				commentList.add(comment);
			}
			
			CommentResult commentResult = new CommentResult();
			commentResult.setCommentList(commentList);
			return commentResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public CommentResult add(CommentDO commentDO) {
		String sql = "insert into comment (article_id, usr_id, content, time) values (?, ?, ?, ?)";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, commentDO.getArticleId());
			ps.setInt(2, commentDO.getUsrId());
			ps.setString(3, commentDO.getContent());
			ps.setTimestamp(4, new Timestamp(commentDO.getTime().getTime()));
			ps.executeUpdate();
			ps.close();
			
			CommentResult commentResult = new CommentResult();
			commentResult.setSuccess(true);
			commentResult.setCommentDO(commentDO);
			return commentResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}

	}

	@Override
	public List<CommentDO> list(CommentDO commentDO, Page page) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT c.*, a.title, a.id as a_id, u.usr, u.id as u_id from comment c, article a, usr u where c.article_id = a.id and c.usr_id = u.id ");
		List<Object> param = new ArrayList<Object>();
		if (commentDO != null && commentDO.getUsrId() != null && commentDO.getUsrId() > 0) {
			sql.append(" and c.usr_id = ?");
			param.add(commentDO.getUsrId());
		}
		sql.append(" order by c.time desc");
		List<Map<String, Object>> returnList = getJdbcBaseDao().findPageList(sql.toString(), param.toArray(), page);
		List<CommentDO> list = new ArrayList<CommentDO>();
		for (Map<String, Object> map : returnList) {
			CommentDO c = new CommentDO();
			c.setContent(map.get("content").toString());
			c.setTime((Date) map.get("time"));
			c.setId((Integer) map.get("id")); 
			c.setArticleId((Integer) map.get("article_id"));
			
			//articleDO是140108做sidebar时新加的，可能和前面的有重复，但应该不会冲突
			ArticleDO articleDO = new ArticleDO();
			articleDO.setId((Integer) map.get("a_id"));
			articleDO.setTitle(map.get("title").toString());
			c.setArticleDO(articleDO);
			
			Usr usr = new Usr();
			usr.setId((Integer) map.get("u_id"));
			usr.setUsr(map.get("usr").toString());
			c.setUsr(usr);
			
			list.add(c);
		}
		return list;
	}

	@Override
	public CommentResult del(CommentDO commentDO) {
		String sql = "delete from comment where id=?";
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, commentDO.getId());
			ps.executeUpdate();
			ps.close();
			
			CommentResult commentResult= new CommentResult();
			commentResult.setSuccess(false);
			return commentResult;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					
				}
			}
		}
	}

	@Override
	public CommentResult byCommentId(CommentDO commentDO) {
		String sql = "select * from comment where id = ?";
		CommentDO _commentDo = simpleJdbcTemplate.queryForObject(sql, 
				new CommentRowMapper(), new Object[] { commentDO.getId() });
		CommentResult commentResult = new CommentResult();
		commentResult.setSuccess(true);
		commentResult.setCommentDO(_commentDo);
		return commentResult;
	}
	
	@Autowired
	private SimpleJdbcTemplate simpleJdbcTemplate;

	public SimpleJdbcTemplate getSimpleJdbcTemplate() {
		return simpleJdbcTemplate;
	}

	public void setSimpleJdbcTemplate(SimpleJdbcTemplate simpleJdbcTemplate) {
		this.simpleJdbcTemplate = simpleJdbcTemplate;
	}


}
